package com.yyedu.examol.dao;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Properties;

import com.yyedu.examol.util.Configuration;
import com.yyedu.examol.util.SysConstants;

/**
* @ClassName: BaseDAO 
* @Description: 用于完成对数据库的通用的新增、修改、删除、查询动作
* @author Kenny 27905556@qq.com
* @date 2015年9月29日 下午3:25:49 
*
 */
public class BaseDAO {
	private Connection connection = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	
	/** 数据库连接驱动 */
	private final String DRIVER = Configuration.get(SysConstants.DATABASE_DRIVER);
	/** 数据库连接url */
	private final String URL = Configuration.get(SysConstants.DATABASE_URL);
	/** 数据库连接用户名 */
	private final String UNAME = Configuration.get(SysConstants.DATABASE_UNAME);
	/** 数据库连接密码 */
	private final String UPASS = Configuration.get(SysConstants.DATABASE_UPASS);
	
	/**
	 * 构造函数
	 * 主要初始化连接对象和语句对象
	 */
	public BaseDAO(){
		this.initConnectionAndStatement();
	}
	
	/**
	 * 初始化connection和Statement对象
	 */
	public void initConnectionAndStatement(){
		try {
			Class.forName(DRIVER);
			this.connection = DriverManager.getConnection(URL, UNAME, UPASS);
			this.statement = this.connection.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 初始化connection对象
	 */
	public Connection initConnection(){
		try {
			Class.forName(DRIVER);
			this.connection = DriverManager.getConnection(URL, UNAME, UPASS);
			return this.connection;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 释放数据库相关资源
	 * @param rs
	 * @param stmt
	 * @param pstmt
	 * @param conn
	 */
	public void free(ResultSet rs, Statement stmt, PreparedStatement pstmt, Connection conn){
		if(null != rs){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(null != stmt){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(null != pstmt){
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(null != conn){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 用于执行更新操作（增删改）
	 * 返回0更新失败
	 * @param sql
	 * @return
	 */
	public int executeUpdate(String sql){
		//影响的行数
		int effectNum = 0;
		try{
			if(null == this.connection || this.connection.isClosed()){
				this.initConnectionAndStatement();
			}
			effectNum = this.statement.executeUpdate(sql);
		}catch(SQLException ex){
			ex.printStackTrace();
			effectNum = 0;
			//回滚操作
			try {
				this.connection.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}finally{
			this.free(null, this.statement, null, this.connection);
		}
		return effectNum;
	}
	
	/**
	 * 使用PreparedStatement对象执行更新操作
	 * @param preparedSql 执行的sql语句
	 * @param params 所需的参数
	 * @return 返回更新的行数
	 */
	public int executeUpdate(String preparedSql, Object ... params){
		this.connection = this.initConnection();
		try {
			this.preparedStatement = this.connection.prepareStatement(preparedSql);
			if( params != null && params.length != 0) {
                for( int i = 0; i < params.length; i++ ) {
                    this.preparedStatement.setObject(i+1, params[i]);// 为预编译sql设置参数
                }
            }
			return this.preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			this.free(null, null, this.preparedStatement, this.connection);
		}
		return 0;
	}
	/**
	 * 
	 * @param sqlBatch
	 * @return
	 */
	public boolean executeTransaction(String[] sqlBatch){
		this.connection = this.initConnection();
		this.preparedStatement = null;
		try {
			//设置事务的提交方式为非自动提交
			connection.setAutoCommit(false);
			for(String sql : sqlBatch){
				preparedStatement = connection.prepareStatement(sql);
				preparedStatement.executeUpdate();
			}
			//提交事务
			connection.commit();
			return true;
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally{
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			this.free(null, null, this.preparedStatement, this.connection);	
		}
		
		return false;
	}
	/**
	 * 封装调用存储过程的方法
	 * @param callable 存储过程 格式：testCallable(?, ?, ?)
	 * @param inParams 输入参数
	 * @param outIndex 输出参数索引，从1开始
	 * @param outTypes 输出参数类型数组
	 * @return 如果有输出参数，返回输出参数数组
	 */
	public Object[] executeCallable(String callable, String tableName, Class collectionType,Object[] inParams, int[] outIndex, Class[] outTypes){
		Collection collection = null;							//用来装载查询出的实体集合
		Object[] outParamArray = new Object[2];	//用来装载输出参数，第一个元素是实体集合，第二个元素是存储过程输出参数集合
		try {
			collection = (Collection) collectionType.newInstance();
		} catch (InstantiationException | IllegalAccessException e1) {
			e1.printStackTrace();
		}
		connection = initConnection();
		CallableStatement calStmt = null;
		ResultSet rs = null;
		try {
			//{call testCallable(?, ?, ?)}
			calStmt = connection.prepareCall("{call " + callable + "}");
			//为存储过程的输出参数赋值
			if(inParams != null)
				for (int i = 0; i < inParams.length; i++) {
					calStmt.setObject(i+1, inParams[i]);
				}
			//为存储过程的返回值注册类型
			if(outIndex != null){
				for (int i = 0; i < outIndex.length; i++) {
					switch(outTypes[i].getName()){
					case "java.lang.Integer":case "int":
						calStmt.registerOutParameter(outIndex[i], Types.INTEGER);
						System.out.println("calStmt.registerOutParameter(outIndex["+"i"+"], Types.INTEGER);");
						break;
					case "java.lang.String":
						calStmt.registerOutParameter(outIndex[i], Types.VARCHAR);
						break;
					case "java.lang.Double":
						calStmt.registerOutParameter(outIndex[i], Types.DOUBLE);
						break;
					case "java.lang.Float":
						calStmt.registerOutParameter(outIndex[i], Types.FLOAT);
						break;
					case "java.lang.Boolean":
						calStmt.registerOutParameter(outIndex[i], Types.BOOLEAN);
						break;
					case "java.lang.Long":
						calStmt.registerOutParameter(outIndex[i], Types.BIGINT);
						break;
					case "java.lang.Short":
						calStmt.registerOutParameter(outIndex[i], Types.SMALLINT);
						break;
					default:
						calStmt.registerOutParameter(outIndex[i], Types.OTHER);
					}
				}
			}
			
			//读取结果集和返回值
			rs = calStmt.executeQuery();
			while(rs.next()){
				collection.add(this.mappingEntity(rs, tableName));
			}
			outParamArray[0] = collection;
			Object[] outputParams = new Object[outIndex.length];
			if(outIndex != null){//如果有输出参数
				for (int i = 0; i < outIndex.length; i++) {
					outputParams[i] = calStmt.getObject(outIndex[i]);
				}
			}
			outParamArray[1] = outputParams;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return outParamArray;
	}
	
	/**
	 * 该方法负责通用的查询表中所有内容。这个方法使用反射技术来实现
	 * 这样可以简化客户端代码
	 * 所有表的通用查询可以输入表名，然后通过Facade对象得到一个实体集合
	 * @return
	 */
	public Collection findBySql(String sql, String tableName, Class collectionType){	
		ResultSet resultSet = null;
		Collection collection = null;
		try {
			collection = (Collection) collectionType.newInstance();
			if(null == this.connection || this.connection.isClosed()){
				this.initConnectionAndStatement();
			}
			//获取结果集对象
			resultSet = this.statement.executeQuery(sql);
			//ResultSetMetaData rsMetaData = resultSet.getMetaData();
			//获取表中的字段总数
			//int colums = rsMetaData.getColumnCount();
			while(resultSet.next()){
				collection.add(this.mappingEntity(resultSet, tableName));
			}
			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally{
			this.free(resultSet, this.statement, null, this.connection);
		}
		return collection;
	}
	/**
	 * 用来将结果集中的某行数据填充到实体类中
	 * @param rs
	 * @param fillObj
	 * @param tableName
	 * @throws SQLException
	 * @throws IOException 
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 */
	public Object mappingEntity(ResultSet resultSet, String tableName) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{		
		String pojoName = Configuration.get(tableName);		
		Class<?> clazz = Class.forName(pojoName);
		//通过反射实例化tableName对应的实体对象
		Object objPojo = clazz.newInstance();
		for(Method method : clazz.getMethods()){
			String methodName = method.getName();
			if("set".equals(methodName.substring(0, 3))){
				String fieldName = methodName.substring(3).toLowerCase();
				String paramType = method.getParameterTypes()[0].getTypeName();
				if("int".equals(paramType) || "java.lang.Integer".equals(paramType)){
					method.invoke(objPojo, resultSet.getInt(fieldName));
				}else if("long".equals(paramType) || "java.lang.Long".equals(paramType)){
					method.invoke(objPojo, resultSet.getLong(fieldName));
				}else if("short".equals(paramType) || "java.lang.Short".equals(paramType)){
					method.invoke(objPojo, resultSet.getShort(fieldName));
				}else if("double".equals(paramType) || "java.lang.Double".equals(paramType)){
					method.invoke(objPojo, resultSet.getDouble(fieldName));
				}else if("float".equals(paramType) || "java.lang.Float".equals(paramType)){
					method.invoke(objPojo, resultSet.getFloat(fieldName));
				}else if("boolean".equals(paramType) || "java.lang.Boolean".equals(paramType)){
					method.invoke(objPojo, resultSet.getBoolean(fieldName));
				}else if("Date".equals(paramType) || "java.util.Date".equals(paramType)){
					method.invoke(objPojo, resultSet.getDate(fieldName));
				}else if("String".equals(paramType) || "java.lang.String".equals(paramType)){
					method.invoke(objPojo, resultSet.getString(fieldName));
				}else{
					//其他情况候补
				}
			}
			
		}
		return objPojo;
	}
}
